/*
 * Copyright (c) 2008-2021 浩瀚深度 All Rights Reserved.
 *
 * <p>FileName: SqlServiceImpl.java</p>
 *
 * @author zz
 * @date 2024/2/21
 * @version 1.0
 * History:
 * v1.0.0 2024/2/21 Create
 */
package com.sqlweb.sqlweb.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import com.sqlweb.sqlweb.base.BusinessException;
import com.sqlweb.sqlweb.base.Result;
import com.sqlweb.sqlweb.base.ResultCode;
import com.sqlweb.sqlweb.config.DataSourceContextHolder;
import com.sqlweb.sqlweb.entity.DataSourceConfig;
import com.sqlweb.sqlweb.entity.ExecuteSql;
import com.sqlweb.sqlweb.mapper.SqlExecuteLogMapper;
import com.sqlweb.sqlweb.service.DataSourceService;
import com.sqlweb.sqlweb.service.SqlService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>Title: SqlServiceImpl</p>
 * <p>Description: </p>
 *
 * @author zz
 */
@Slf4j
@Service
public class SqlServiceImpl implements SqlService {
    @Autowired
    SqlExecuteLogMapper sqlExecuteLogMapper;
    @Autowired
    private DataSourceService dataSourceService;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Override
    public Object executeSql(ExecuteSql executeSql) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        List<DataSourceConfig> dataSourceConfigs = dataSourceService.selectAll();
        Map<Integer, String> dbMap = new HashMap<>(16);
        if (CollectionUtil.isNotEmpty(dataSourceConfigs)) {
            dbMap = dataSourceConfigs.stream()
                    .collect(Collectors.toMap(
                            DataSourceConfig::getId,
                            DataSourceConfig::getJdbcUrl,
                            // 在键冲突时选择后者
                            (existing, replacement) -> replacement
                    ));

            if (dbMap.containsKey(executeSql.getDataSourceId())) {
                executeSql.setDataSourceName(dbMap.get(executeSql.getDataSourceId()));
            }
        }
        Date dateNow = new Date();
        String timestamp = dateFormat.format(dateNow);
        executeSql.setCreateTime(dateNow);
        // 用来存储查询结果或更新计数
        Object resultData = null;
        try {
            DataSourceContextHolder.setDataSourceKey(executeSql.getDataSourceId());
            if (executeSql.getSqlStatement().trim().toLowerCase().startsWith("select")) {
                List<Map<String, Object>> queryResults = jdbcTemplate.queryForList(executeSql.getSqlStatement());
                String filePath = writeToCsvFile(queryResults, timestamp);
                executeSql.setResultFile(filePath);
                // 将查询结果赋给resultData
                resultData = queryResults;
            } else if (executeSql.getSqlStatement().trim().toLowerCase().startsWith("update")) {
                int updateCount = jdbcTemplate.update(executeSql.getSqlStatement());
                // 将更新计数赋给resultData
                resultData = updateCount;
            } else {
                return new Result(ResultCode.ERROR, "不支持的SQL类型!");
            }
        } catch (Exception e) {
            log.error("执行失败:", e);
            return new Result(ResultCode.ERROR, "执行失败: " + e.getMessage());
        } finally {
            DataSourceContextHolder.clearDataSourceKey();
            // 保存执行记录
            try {
                sqlExecuteLogMapper.insert(executeSql);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return resultData;
    }

    @Override
    public void removeSqlHis(ExecuteSql executeSql) {
        ExecuteSql executeSqlResult = sqlExecuteLogMapper.selectById(executeSql.getId());
        if (executeSqlResult != null && executeSqlResult.getResultFile() != null) {
            Path filePath = Paths.get(executeSqlResult.getResultFile());
            // 检查文件是否存在
            if (Files.exists(filePath)) {
                try {
                    // 尝试删除文件
                    Files.delete(filePath);
                } catch (Exception e) {
                    log.error("删除执行记录失败:" + e);
                    throw new BusinessException("删除文件失败");
                }
            }
        }
        sqlExecuteLogMapper.deleteById(executeSql.getId());
    }

    private String writeToCsvFile(List<Map<String, Object>> data, String timestamp) throws Exception {
        if (data.isEmpty()) {
            // 如果没有数据，则没有列名可以写入
            return null;
        }

        String fileName = "query-results-" + timestamp + ".csv";
        File csvFile = new File(fileName);

        try (BufferedWriter writer = new BufferedWriter(new FileWriter(csvFile))) {
            // 获取并写入列名
            Set<String> columnNames = data.get(0).keySet();
            writer.write(String.join(",", columnNames));
            writer.newLine();

            // 写入数据
            for (Map<String, Object> row : data) {
                String line = row.values().stream()
                        .map(v -> v == null ? "" : v.toString().replace(",", "\\,"))
                        .collect(Collectors.joining(","));
                writer.write(line);
                writer.newLine();
            }
        }
        return csvFile.getAbsolutePath();
    }
}
